package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.telecom.cms.dao.SysLocLatnDAO;
import com.telecom.cms.dto.CampContRslt;
import com.telecom.cms.dto.CampContRsltEx;
import com.telecom.cms.util.Constant;
import com.telecom.cms.util.CurrentTime;

public class CampContRsltDAO {

	Dao dao = new DaoImp();

	String db = (new Constant()).getPara("DATABASE");

	public int insert(CampContRslt transientInstance) {
		int rs = 0;
		//System.out.println("when saving,custid is :"+transientInstance.getCustId());
		String sql = "select max(cont_seq_id) from  " + db
				+ ".Camp_Cont_Rslt_v where camp_Id="
				+ transientInstance.getCampId() + " and camp_seq_id="
				+ transientInstance.getCampSeqId() + " and cust_id='"+transientInstance.getCustId()+"' and prd_inst_id='"
				+ transientInstance.getPrdInstId() + "'";
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next()&&rs2.getString(1)!=null)
				sql = "insert into " + db + ".Camp_Cont_Rslt " +
						"values('"
						+ transientInstance.getLatnId() + "' , "
						+ transientInstance.getCampId() + ", "
						+ transientInstance.getCampSeqId() + " , '"
						+ transientInstance.getCustId() + "' , '"
						+ transientInstance.getPrdInstId() + "' ,  "
						+ (rs2.getInt(1)+1) + ", " + "timestamp '"
						+ CurrentTime.getCurrentTime() + "', '"
						+ transientInstance.getUserId() + "' ,  "
						+ transientInstance.getCampContRsltLvlId() + "  ,  "
						+ (transientInstance.getAppointTime().equals("")?"null":"timestamp '" + transientInstance.getAppointTime()+"'")
						+ ", '" + transientInstance.getRemark() + "' , '"
						+ transientInstance.getPromName() + "')";
						/*"select Latn_Id,Camp_Id,Camp_Seq_Id,Cust_Id,Prd_Inst_Id,max(Cont_Seq_Id)+1,timestamp '"
						+ CurrentTime.getCurrentTime()+"','"
						+ transientInstance.getUserId() + "' ,  "
						+ transientInstance.getCampContRsltLvlId() + "  ,  "
						+ (transientInstance.getAppointTime().equals("")?"null":"timestamp '" + transientInstance.getAppointTime()+"'")
						+ ", '" + transientInstance.getRemark() + "' , '"
						+ transientInstance.getPromName() + "' from " + db + ".Camp_Cont_Rslt_v where camp_Id="
						+ transientInstance.getCampId() + " and camp_seq_id="
						+ transientInstance.getCampSeqId() + " and cust_id='"+transientInstance.getCustId()+"' and prd_inst_id='"
						+ transientInstance.getPrdInstId() + "' group by 1,2,3,4,5";*/
			else
				sql = "insert into " + db + ".Camp_Cont_Rslt values('"
						+ transientInstance.getLatnId() + "' , '"
						+ transientInstance.getCampId() + "', '"
						+ transientInstance.getCampSeqId() + "' , '"
						+ transientInstance.getCustId() + "' , '"
						+ transientInstance.getPrdInstId() + "' ,  " + "1  , "
						+ "timestamp '" + CurrentTime.getCurrentTime()
						+ "', '" + transientInstance.getUserId() + "' ,  "
						+ transientInstance.getCampContRsltLvlId() + "  ,"
						+ (transientInstance.getAppointTime().equals("")?"":"timestamp '" + transientInstance.getAppointTime()+"'")
						+ ", '" + transientInstance.getRemark() + "' , '"
						+ transientInstance.getPromName() + " ')";
			rs = dao.insert(sql);
			
			if (rs2 != null) {

				rs2.close();
			}

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		finally
		{
			dao.closeconn();
		}
		return rs;

	}

	/*public void delete(CampContRslt persistentInstance) {

		String sql = "delete from " + db
				+ ".Camp_Cont_Rslt where camp_Seq_Id = "
				+ persistentInstance.getCampSeqId();

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}*/
	
	public void inserttrace(CampContRslt campcontrslt,String userid) {
		int channeltype=10;
		switch(campcontrslt.getCampContRsltLvlId())
		{
		case  501:break;
		case  502:channeltype=40;break;
		case  503:channeltype=20;break;
		}
		String latncd=(new SysLocLatnDAO()).findById(campcontrslt.getLatnId()).getLatn_Cd();
		String sql = "insert into " + db
				+ ".CAMP_TARGET_FINISH_TRACE(Camp_Id,camp_name,Camp_Seq_Id,Prd_Inst_Id,user_contact_nbr,Accs_Nbr,user_name,User_id,Finish_State," +
						"Finish_Date,Cust_Inter_Channel_Id,Latn_Id,create_time) select "
				+campcontrslt.getCampId()+",b.camp_name,"+campcontrslt.getCampSeqId()+",'"+campcontrslt.getPrdInstId()
				+"',a.Rec_Contact_Accs_Nbr,a.Rec_Contact_Accs_Nbr,a.cust_name,"+userid+",'Y',Date '"+CurrentTime.getCurrentDate2()
				+"','"+channeltype+"','"+campcontrslt.getLatnId()+"',timestamp '"+CurrentTime.getCurrentTime()
				+"' from " + db+".Camp_Camp b left join "+(new Constant()).getPara("BAS_CUST_TABLE")
				+" a on a.cust_id='"+campcontrslt.getCustId()+"'"
				+" where b.camp_id="+campcontrslt.getCampId();
		sql=(new Constant()).changeLatnId(latncd, sql);

		try {
			dao.insert(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	public void insertinf(CampContRslt campcontrslt,String userid) {

		int channeltype=10;
		switch(campcontrslt.getCampContRsltLvlId())
		{
		case  501:break;
		case  502:channeltype=40;break;
		case  503:channeltype=20;break;
		}
		String latncd=(new SysLocLatnDAO()).findById(campcontrslt.getLatnId()).getLatn_Cd();
		String sql = "insert into " + db
				+ ".CAMP_CONT_RSLT_INTF(Camp_Id,camp_name,Camp_Seq_Id,cust_id,Prd_Inst_Id,Accs_Nbr,user_name,User_id," +
						"Cust_Inter_Channel_Id,Latn_Id,create_time) select "
				+campcontrslt.getCampId()+",b.camp_name,"+campcontrslt.getCampSeqId()+",'"+campcontrslt.getPrdInstId()
				+"','"+campcontrslt.getCustId()+"',a.Contact_Telephone,a.cust_name,"+userid
				+",'"+channeltype+"','"+campcontrslt.getLatnId()+"',timestamp '"+CurrentTime.getCurrentTime()
				+"' from " + db+".Camp_Camp b left join  "+(new Constant()).getPara("BAS_CUST_TABLE")
				+" a on a.cust_id='"+campcontrslt.getCustId()+"'"
				+" where b.camp_id="+campcontrslt.getCampId();
			sql=(new Constant()).changeLatnId(latncd, sql);
		try {
			dao.insert(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public CampContRsltEx findById(String campid, String campseqid,String custid,
			 String prdinstid, String contseqid) {

		String sql = "select a.*,b.code_name from " + db + ".Camp_Cont_Rslt_v a " +
				"left join " + db + ".sys_code b on a.Camp_Cont_Rslt_Lvl_Id=b.code_id and b.code_type='Camp_Cont_Rslt_Lvl' where a.camp_Id="
				+ campid + " and a.camp_seq_id=" + campseqid + "  and a.prd_inst_id"
				+"='" + prdinstid+ "' and a.cust_id='"+custid+"' and  a.cont_seq_id=" + contseqid+"   ";
		CampContRsltEx sd = new CampContRsltEx();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));
				sd.setCustId(rs.getString(4));
				sd.setPrdInstId(rs.getString(5));
				sd.setContSeqId(rs.getInt(6));
				sd.setContTime(rs.getString(7));
				sd.setUserId(rs.getString(8));
				sd.setCampContRsltLvlId(rs.getInt(9));
				sd.setAppointTime(rs.getString(10));
				sd.setRemark(rs.getString(11));
				sd.setPromName(rs.getString(12));
				sd.setCampContRsltLvlName(rs.getString(13));
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return sd;

	}

	public List findAll() {

		String sql = "select * from " + db + ".Camp_Cont_Rslt_v";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampContRslt sd = new CampContRslt();
				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));
				sd.setCustId(rs.getString(4));
				sd.setPrdInstId(rs.getString(5));
				sd.setContSeqId(rs.getInt(6));
				sd.setContTime(rs.getString(7));
				sd.setUserId(rs.getString(8));
				sd.setCampContRsltLvlId(rs.getInt(9));
				sd.setAppointTime(rs.getString(10));
				sd.setRemark(rs.getString(11));
				sd.setPromName(rs.getString(12));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;
	}

	public List findAll(String campid, String campseqid,String custid,String prdinstid,String userid) {

		String sql = "select a.*,b.code_name from " + db + ".Camp_Cont_Rslt_v a " +
			"left join " + db + ".sys_code b on a.Camp_Cont_Rslt_Lvl_Id=b.code_id  and b.code_type='Camp_Cont_Rslt_Lvl' where a.camp_Id="
			+ campid + " and a.camp_seq_id=" + campseqid + "  and a.cust_id"
			+"='" + custid+ "' and a.prd_inst_id='" + prdinstid+"' and a.User_id='" + userid+ 
					"'  order by cont_seq_id desc";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampContRsltEx sd = new CampContRsltEx();
				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));
				sd.setCustId(rs.getString(4));
				sd.setPrdInstId(rs.getString(5));
				sd.setContSeqId(rs.getInt(6));
				sd.setContTime(rs.getString(7));
				sd.setUserId(rs.getString(8));
				sd.setCampContRsltLvlId(rs.getInt(9));
				sd.setAppointTime(rs.getString(10));
				sd.setRemark(rs.getString(11));
				sd.setPromName(rs.getString(12));
				sd.setCampContRsltLvlName(rs.getString(13));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;
	}
}
